package cn.threewaters.qtyy.web.controller.business;

import java.io.IOException;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import cn.threewaters.qtyy.dic.one.queryparam.QueryLq;
import cn.threewaters.qtyy.dic.one.queryresult.ResultLq;
import cn.threewaters.qtyy.service.business.LqService;
import cn.threewaters.qtyy.util.excel.ExcelUtil;
import cn.threewaters.qtyy.web.facade.Point;
import cn.threewaters.qtyy.web.facade.TempAnaly;
import io.swagger.annotations.ApiOperation;
import springfox.documentation.annotations.ApiIgnore;

@Controller
public class ExportController {

	@Resource
	@Qualifier("oneJdbcTemplate")
	private JdbcTemplate oneJdbcTemplate;

	@Autowired
	private LqService lqService;

	@ApiIgnore
	@ApiOperation(value = "导出")
	@RequestMapping(value = "/excel")
	public void download(HttpServletRequest request, HttpServletResponse response, String orgcode, String housecode)
			throws IOException {
		QueryLq queryLq = new QueryLq();
		queryLq.setOrgcode(orgcode);
		queryLq.setStorehousecode(housecode);
		Pagination pagination = new Pagination();
		pagination.setCurrent(1);
		pagination.setSize(1000);
		List<ResultLq> lqXQs = lqService.queryLqXQ(queryLq, pagination);
		ResultLq resultLq = new ResultLq();
		if (lqXQs.size() > 0) {
			resultLq = lqXQs.get(0);
			String tempdata = resultLq.getTempdata();
			TempAnaly tempAnaly = new TempAnaly();
			Point[][][] analysis = tempAnaly.analysis(tempdata,
					Integer.parseInt(resultLq.getCablerow() == null ? "0" : resultLq.getCablerow()),
					Integer.parseInt(resultLq.getCablecolum() == null ? "0" : resultLq.getCablecolum()),
					Integer.parseInt(resultLq.getCabletier() == null ? "0" : resultLq.getCabletier()),
					resultLq.getStapoint(), resultLq.getSortdir(), resultLq.getPointrule(), resultLq.getSortrule());
			resultLq.setPoints(analysis);

			List<Point> pointList = Lists.newArrayList();
			for (int i = 0; i < analysis.length; i++) {
				for (int j = 0; j < analysis[i].length; j++) {
					for (int k = 0; k < analysis[i][j].length; k++) {
						pointList.add(analysis[i][j][k]);
					}
				}
			}

			for (int k = 0; k < analysis[0][0].length; k++) {
				double high = 0;
				double low = 0;
				double sum = 0;
				double index = 0;
				for (int i = 0; i < analysis.length; i++) {
					for (int j = 0; j < analysis[i].length; j++) {
						double parseDouble = Double
								.parseDouble(analysis[i][j][k].getTemp() == null ? "0" : analysis[i][j][k].getTemp());
						if (high < parseDouble) {
							high = parseDouble;
						}
						if (low > parseDouble || low == 0) {
							low = parseDouble;
						}
						sum = sum + parseDouble;
						index = index + 1;
					}
				}
				DecimalFormat df = new DecimalFormat("######0.0");
				if (k == 0) {
					resultLq.setOneavg(String.valueOf(df.format(sum / index)));
					resultLq.setOnehigh(String.valueOf(high));
					resultLq.setOnelow(String.valueOf(low));
				}
				if (k == 1) {
					resultLq.setTwoavg(String.valueOf(df.format(sum / index)));
					resultLq.setTwohigh(String.valueOf(high));
					resultLq.setTwolow(String.valueOf(low));
				}
				if (k == 2) {
					resultLq.setThreeavg(String.valueOf(df.format(sum / index)));
					resultLq.setThreehigh(String.valueOf(high));
					resultLq.setThreelow(String.valueOf(low));
				}
				if (k == 3) {
					resultLq.setFouravg(String.valueOf(df.format(sum / index)));
					resultLq.setFourhigh(String.valueOf(high));
					resultLq.setFourlow(String.valueOf(low));
				}
			}

			resultLq.setPointList(pointList);
		}
		String hightemp = resultLq.getHighttemp();
		String lowtemp = resultLq.getLowertemp();

		// 开始写入excel
		HSSFWorkbook workbook = ExcelUtil.createWorkbook();
		// 标题
		HSSFCellStyle headStyle = ExcelUtil.initColumnHeadStyle(workbook);
		// 天气
		HSSFCellStyle head2Style = ExcelUtil.initColumnHead2Style(workbook);
		// 正文
		HSSFCellStyle centerStyle = ExcelUtil.initColumnCenterstyle(workbook);
		// 粮情分析
		HSSFCellStyle center2Style = ExcelUtil.initColumnCenter2style(workbook);
		// 签字
		HSSFCellStyle center3Style = ExcelUtil.initColumnCenter3style(workbook);

		HSSFCellStyle center3StyleHigh = ExcelUtil.initColumnCenterstyleHigh(workbook);

		HSSFCellStyle center3StyleLow = ExcelUtil.initColumnCenterstyleLow(workbook);

		// 数据准备：
		// 仓信息
		List<Map<String, Object>> houseInfoList = oneJdbcTemplate.queryForList(
				"select * from la_storehouse where housecode = '" + housecode + "' and orgcode = '" + orgcode + "'");
		Map<String, Object> houseInfo = Maps.newHashMap();
		if (houseInfoList.size() > 0) {
			houseInfo = houseInfoList.get(0);
		}
		// 天气信息
		List<Map<String, Object>> whetherInfoList = oneJdbcTemplate
				.queryForList("select * from la_weather order by id desc limit 1");
		Map<String, Object> whetherInfo = Maps.newHashMap();
		if (whetherInfoList.size() > 0) {
			whetherInfo = whetherInfoList.get(0);
		}

		int minColumn = 9;
		int realColumn = Integer
				.parseInt(String.valueOf(houseInfo.get("cablecolum") == null ? 0 : houseInfo.get("cablecolum")));
		if (realColumn < minColumn) {
			realColumn = minColumn;
		}
		HSSFSheet sheet = ExcelUtil.createSheet(workbook, "明细");
		int rowIndex = 0;
		int columnIndex = 0;
		// 写标题
		ExcelUtil.mergeCell(sheet, rowIndex, rowIndex++, columnIndex, realColumn, "中央储备粮宿州直属库粮情报表", headStyle);
		// 天气信息
		String houseName = (String) houseInfo.get("housename");
		String whether = (String) whetherInfo.get("rainsnow");
		String direction = (String) whetherInfo.get("direction");
		String title2 = "仓号：" + houseName + "   天气：" + whether + "   风向：" + direction + "   检测时间："
				+ resultLq.getChecktime();

		int cableRow = Integer.parseInt(resultLq.getCabletier() == null ? "0" : resultLq.getCabletier());

		ExcelUtil.mergeCell(sheet, rowIndex, rowIndex++, columnIndex, realColumn, title2, head2Style);
		Point[][][] tempData = (Point[][][]) resultLq.getPoints();
		// 明细
		for (int i = 0; i < tempData.length; i++) {
			for (int k = 0; k < tempData[i][0].length; k++) {
				columnIndex = 1;
				if (k == 0) {
					ExcelUtil.writeCell(sheet, rowIndex, 0, "", centerStyle);
					for (int j = 0; j < ((tempData[i].length <= minColumn) == true ? minColumn
							: tempData[i].length); j++) {
						try {
							ExcelUtil.writeCell(sheet, rowIndex, columnIndex, tempData[i][j][k].getLan(), centerStyle);
							columnIndex++;
						} catch (Exception e) {
							ExcelUtil.writeCell(sheet, rowIndex, columnIndex++, "-", centerStyle);
						}
					}
					rowIndex++;
					columnIndex = 1;
				}
				if (cableRow == 3) {
					if (k == 0) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "上层", centerStyle);
					} else if (k == 1) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "中层", centerStyle);
					} else if (k == 2) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "下层", centerStyle);
					}
				} else if (cableRow == 4) {
					if (k == 0) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "上层", centerStyle);
					} else if (k == 1) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "中上层", centerStyle);
					} else if (k == 2) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "中下层", centerStyle);
					} else if (k == 3) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "下层", centerStyle);
					}
				} else if (cableRow == 5) {
					if (k == 0) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "上层", centerStyle);
					} else if (k == 1) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "中上层", centerStyle);
					} else if (k == 2) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "中层", centerStyle);
					} else if (k == 3) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "中下层", centerStyle);
					} else if (k == 4) {
						ExcelUtil.writeCell(sheet, rowIndex, 0, "下层", centerStyle);
					}
				} else {
					ExcelUtil.writeCell(sheet, rowIndex, 0, "第" + (k + 1) + "层", centerStyle);
				}

				for (int j = 0; j < ((tempData[i].length <= minColumn) == true ? minColumn : tempData[i].length); j++) {
					try {
						if (hightemp != null && hightemp.equals(tempData[i][j][k].getTemp())) {
							ExcelUtil.writeCell(sheet, rowIndex, columnIndex, tempData[i][j][k].getTemp(),
									center3StyleHigh);
						} else if (lowtemp != null && lowtemp.equals(tempData[i][j][k].getTemp())) {
							ExcelUtil.writeCell(sheet, rowIndex, columnIndex, tempData[i][j][k].getTemp(),
									center3StyleLow);
						} else {
							ExcelUtil.writeCell(sheet, rowIndex, columnIndex, tempData[i][j][k].getTemp(), centerStyle);
						}
						columnIndex++;
					} catch (Exception e) {
						ExcelUtil.writeCell(sheet, rowIndex, columnIndex++, "-", centerStyle);
					}
				}
				rowIndex++;
			}
		}
		columnIndex = 0;
		// 分析
		ExcelUtil.mergeCell(sheet, rowIndex, rowIndex = rowIndex + 5, columnIndex, realColumn, "粮情分析：", center2Style);
		rowIndex++;
		// 签名
		ExcelUtil.mergeCell(sheet, rowIndex, rowIndex++, columnIndex, realColumn,
				"组长：                  科长：                   ", center3Style);
		// 汇总信息
		int tempRowIndex = rowIndex;
		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "仓温", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getIntemp(), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "全仓平均", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getAvgtemp(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "全仓最高", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getHighttemp(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "全仓最低", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getLowertemp(), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "上层平均", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getOneavg(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "上层最高", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getOnehigh(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 0, "上层最低", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 1, resultLq.getOnelow(), centerStyle);

		tempRowIndex = rowIndex;
		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "仓湿", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getInhumidity(), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "中上层平均", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getTwoavg(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "中上层最高", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getTwohigh(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "中上层最低", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getTwolow(), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "中下层平均", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getThreeavg(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "中下层最高", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getThreehigh(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 2, "中下层最低", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 3, resultLq.getThreelow(), centerStyle);

		tempRowIndex = rowIndex;
		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "气温", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, (String) whetherInfo.get("outtemp"), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "粮食品种", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, (String) houseInfo.get("grainvarietyname"), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "发芽率", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, "", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "容重/出糙", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, "", centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "下层平均", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, resultLq.getFouravg(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "下层最高", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, resultLq.getFourhigh(), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 4, "下层最低", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 5, resultLq.getFourlow(), centerStyle);

		tempRowIndex = rowIndex;
		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "气湿", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, (String) whetherInfo.get("outhumidity"), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "设计容量T", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, String.valueOf(houseInfo.get("designcapacity")), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "面筋持水率", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, "", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "不完善率", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, "", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "杂质", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, "", centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "检测员", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, (String) houseInfo.get("checkername"), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 6, "保管员", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 7, (String) houseInfo.get("keepername"), centerStyle);

		tempRowIndex = rowIndex;
		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "仓房类型", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, (String) houseInfo.get("typename"), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "实际存数T", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, String.valueOf(houseInfo.get("confirmcapacity")), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "入库水分", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, (String) houseInfo.get(""), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "当前水分", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, "", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "产地", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, (String) houseInfo.get("produceplace"), centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "主管科长", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, (String) houseInfo.get("kezname"), centerStyle);

		ExcelUtil.writeCell(sheet, tempRowIndex, 8, "主管主任", centerStyle);
		ExcelUtil.writeCell(sheet, tempRowIndex++, 9, (String) houseInfo.get("mgrname"), centerStyle);

		String fileName = new String(("CWMX" + resultLq.getStorehousecode() + ".xls").getBytes("UTF-8"), "ISO-8859-1");// 为了解决中文名称乱码问题
		response.setContentType("multipart/form-data");
		response.setHeader("Content-disposition", "attachment;filename=" + fileName);
		OutputStream ouputStream = response.getOutputStream();
		workbook.write(ouputStream);
		ouputStream.flush();
		ouputStream.close();
	}
}
